﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;
namespace DAO
{
    public class NhanVienDAO
    {
        //Load danh sach sinh vien
        public static DataTable LoadDSNhanVien()
        {
            SqlConnection cn = Connection.KetNoi();
            SqlCommand cmd = new SqlCommand("sp_LoadDSNhanVien",cn);
            cmd.CommandType = CommandType.StoredProcedure;
            //Thuc hien lay ket qua ve
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        //Them mot nhan vien moi
        public static void ThemNhanVien(NhanVienDTO nhanvienDTO)
        {
            SqlConnection cn = Connection.KetNoi();
            SqlCommand cmd = new SqlCommand("sp_ThemNhanVien",cn);
            cmd.CommandType = CommandType.StoredProcedure;

            //Khai bao cac parameters
            cmd.Parameters.Add("@MaNV",SqlDbType.NVarChar,10);
            cmd.Parameters.Add("@HoTen",SqlDbType.NVarChar,100);
            cmd.Parameters.Add("@NgaySinh",SqlDbType.DateTime);
            cmd.Parameters.Add("@CMND", SqlDbType.NVarChar, 10);
            cmd.Parameters.Add("@GioiTinh", SqlDbType.NVarChar, 5);
            cmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar, 200);
            cmd.Parameters.Add("@MaChucVu", SqlDbType.NVarChar, 10);
            cmd.Parameters.Add("@MaTinhTrang", SqlDbType.Int);

            //Gan gia tri
            cmd.Parameters["@MaNV"].Value = nhanvienDTO.MaNV;
            cmd.Parameters["@HoTen"].Value = nhanvienDTO.HoTen;
            cmd.Parameters["@NgaySinh"].Value = nhanvienDTO.NgaySinh;
            cmd.Parameters["@CMND"].Value = nhanvienDTO.CMND;
            cmd.Parameters["@GioiTinh"].Value = nhanvienDTO.GioiTinh;
            cmd.Parameters["@DiaChi"].Value = nhanvienDTO.DiaChi;
            cmd.Parameters["@MaChucVu"].Value = nhanvienDTO.MaChucVu;
            cmd.Parameters["@MaTinhTrang"].Value = nhanvienDTO.MaTinhTrang;

            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        //Xoa mot nhan vien
        public static void XoaNhanVien(string manv)
        {
            //Tao ket noi den csdl
            SqlConnection cn = Connection.KetNoi();
            SqlCommand cmd = new SqlCommand("sp_XoaNhanVien",cn);
            //Dinh nghi cmd la 1 stored procedured
            cmd.CommandType = CommandType.StoredProcedure;

            //Khai bao parameters
            cmd.Parameters.Add("@MaNV",SqlDbType.NVarChar,10);
            //Gan gia tri cho paramaters
            cmd.Parameters["@MaNV"].Value = manv;
            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        //Sua mot nhan vien
        public static void SuaNhanVien(NhanVienDTO nhanvienDTO)
        {
            SqlConnection cn = Connection.KetNoi();
            SqlCommand cmd = new SqlCommand("sp_SuaNhanVien", cn);
            cmd.CommandType = CommandType.StoredProcedure;

            //Khai bao cac parameters
            cmd.Parameters.Add("@MaNV", SqlDbType.NVarChar, 10);
            cmd.Parameters.Add("@HoTen", SqlDbType.NVarChar, 100);
            cmd.Parameters.Add("@NgaySinh", SqlDbType.DateTime);
            cmd.Parameters.Add("@CMND", SqlDbType.NVarChar, 10);
            cmd.Parameters.Add("@GioiTinh", SqlDbType.NVarChar, 5);
            cmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar, 200);
            cmd.Parameters.Add("@MaChucVu", SqlDbType.NVarChar, 10);
            cmd.Parameters.Add("@MaTinhTrang", SqlDbType.Int);

            //Gan gia tri
            cmd.Parameters["@MaNV"].Value = nhanvienDTO.MaNV;
            cmd.Parameters["@HoTen"].Value = nhanvienDTO.HoTen;
            cmd.Parameters["@NgaySinh"].Value = nhanvienDTO.NgaySinh;
            cmd.Parameters["@CMND"].Value = nhanvienDTO.CMND;
            cmd.Parameters["@GioiTinh"].Value = nhanvienDTO.GioiTinh;
            cmd.Parameters["@DiaChi"].Value = nhanvienDTO.DiaChi;
            cmd.Parameters["@MaChucVu"].Value = nhanvienDTO.MaChucVu;
            cmd.Parameters["@MaTinhTrang"].Value = nhanvienDTO.MaTinhTrang;

            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        //Tim theo Ten nhan vien
        public static DataTable TimTheoTenNV(string tennv)
        {
            SqlConnection cn = Connection.KetNoi();
            SqlCommand cmd = new SqlCommand("sp_TimTheoTenNV",cn);
            cmd.CommandType = CommandType.StoredProcedure;
            //Khai bao parameters 
            cmd.Parameters.Add("@HoTen",SqlDbType.NVarChar,100);
            //Gan gia tri cho parameters
            cmd.Parameters["@HoTen"].Value = tennv;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            //Dung datable de lay ket qua lai
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        //Tim theo MaNV
        //public static DataTable TimTheoMaNV(string manv)
        //{
        //    SqlConnection cn = Connection.KetNoi();
        //    SqlCommand cmd = new SqlCommand("sp_TimTheoTenNV", cn);
        //    cmd.CommandType = CommandType.StoredProcedure;
        //    //Khai bao parameters 
        //    cmd.Parameters.Add("@HoTen", SqlDbType.NVarChar, 100);
        //    //Gan gia tri cho parameters
        //    cmd.Parameters["@HoTen"].Value = tennv;
        //    SqlDataAdapter da = new SqlDataAdapter(cmd);
        //    //Dung datable de lay ket qua lai
        //    DataTable dt = new DataTable();
        //    da.Fill(dt);
        //    return dt;
        //}
    }
}
